package My

import (
	"encoding/json"
	"errors"
	"fmt"
	"db2s/global"
	"strconv"
	"strings"
	"sync/atomic"
)

type TableSql struct{}

func getAtomicValue(val *atomic.Value) []map[string]string {
	if val == nil {
		return nil
	}
	rel := val.Load()
	if rel == nil {
		return nil
	}
	switch rel.(type) {
	case []map[string]string:
		return rel.([]map[string]string)
	default:
		return nil
	}
}

func columnValueConvert(TableColumn []global.TableMeta, cn, s string, logSeq int64) (d string) {
	olds := s
	if strings.Contains(s, "'") {
		s = strings.ReplaceAll(s, "'", "\\'")
	} else {
		s = fmt.Sprintf("'%v'", s)
	}
	if strings.EqualFold(olds, "NULL") {
		return olds
	}
	for _, i := range TableColumn {
		if strings.EqualFold(i.ColumnName, cn) {
			switch func(s string) (d string) {
				if s == "DATE" {
					d = "DATE"
				} else if strings.Contains(s, "TIMESTAMP") {
					d = "TIMESTAMP"
				} else if s == "DATETIME" {
					d = "DATETIME"
				}
				return
			}(strings.ToUpper(i.DataType)) {
			case "DATETIME", "TIMESTAMP":
				d = fmt.Sprintf("date_format(%v,'%%Y-%%m-%%d %%H:%%i:%%s')", s)
			case "DATE":
				d = fmt.Sprintf("date_format(%v,'%%Y-%%m-%%d')", s)
			default:
				d = s
			}
			break
		}
	}
	return
}
func jsonToStruct(s string, data any) (err error) {
	// 解析 JSON 字符串
	err = json.Unmarshal([]byte(s), data)
	if err != nil {
		return
	}
	return
}
func pointFuncParameter(point global.Point) (res string) {
	var p []string
	for _, v := range []string{point.X, point.Y, point.Z} {
		if len(v) == 0 {
			continue
		}
		p = append(p, v)
	}
	res = strings.Join(p, " ")
	return
}
func gisPointStr(s string) (r string) {
	var (
		point global.Point
		Rid   string
	)
	if jsonToStruct(s, &point) != nil {
		return
	}
	if len(point.RID) > 0 {
		Rid = fmt.Sprintf(",%v", point.RID)
	}
	r = fmt.Sprintf("ST_GeomFromText('POINT(%v)' %v)", pointFuncParameter(point), Rid)
	return
}
func gisTypeActive(s string) (r string) {
	var gisType, gisValue string
	if len(s) == 0 || strings.EqualFold(s, "<entry>") || strings.EqualFold(s, "<null>") {
		return "NULL"
	}
	if n := strings.Index(s, "@{"); n != -1 {
		gisType = s[:n]
		gisValue = s[n+1:]
	}
	switch gisType {
	case "POINT":
		r = gisPointStr(gisValue)
	}
	return
}
func nullValueActive(lob bool, s string, details global.TableMeta) (r string) {
	switch {
	case strings.EqualFold(s, "<null>"):
		if lob { //json数据的null值在lob插入是nil
			return fmt.Sprintf("<prepare nil>")
		}
		return fmt.Sprintf("NULL")
	case strings.EqualFold(s, "<entry>") || len(s) == 0:
		switch strings.ToLower(details.ConvertColumnType.Type) {
		case "gis", "decimal", "int":
			return fmt.Sprintf("NULL")
		default:
			if lob {
				return fmt.Sprintf("<prepare entry>")
			}
			return fmt.Sprintf("''")
		}
	default:
		return
	}
}
func specialTypeActive(lob bool, s string, details global.TableMeta) (r string) {
	if len(nullValueActive(lob, s, details)) > 0 {
		return nullValueActive(lob, s, details)
	}
	switch {
	case strings.EqualFold(details.ConvertColumnType.Type, "time"):
		return fmt.Sprintf("'%v'", s)
	case strings.EqualFold(details.ConvertColumnType.Type, "timestamp"):
		// MySQL不允许向timestamp数据类型中插入带时区的日期database error is Error 1292 (22007): Incorrect datetime value: '2024-09-26 11:27:28 +0800 08:00' for column 'V3' at row 1
		return fmt.Sprintf("'%v'", strings.TrimSpace(strings.Split(s, " +")[0]))
	case strings.EqualFold(details.ConvertColumnType.Type, "gis"):
		return gisTypeActive(s)
	default:
		r = s
		switch {
		case strings.EqualFold(details.DataType, "json") && lob:
			return fmt.Sprintf("%v", s)
		default:
			if !lob {
				return fmt.Sprintf("'%v'", s)
			}
		}
	}
	return
}

/*
LeftStitching 生成sql 语句where left 条件
*/
func LeftStitching(s global.WhereGenerateInput) string {
	var (
		whereLeft []string
	)
	for _, v := range getAtomicValue(s.LeftDirection) {
		columnName, nLeftOk := v["columnName"]
		columnDataLeft, cLeftOk := v["columnDataLeft"]
		querySymbolLeft, qLeftOk := v["querySymbolLeft"]
		if cLeftOk && qLeftOk && nLeftOk {
			if strings.EqualFold(querySymbolLeft, "=") && strings.EqualFold(columnDataLeft, "NULL") { //处理等值null
				whereLeft = append(whereLeft, fmt.Sprintf("`%v` %v %v", columnName, " IS ", columnDataLeft))
			} else if strings.EqualFold(querySymbolLeft, ">") && strings.EqualFold(columnDataLeft, "NULL") {
				whereLeft = append(whereLeft, fmt.Sprintf("`%v` %v %v", columnName, " IS NOT ", columnDataLeft))
			} else {
				whereLeft = append(whereLeft, fmt.Sprintf("`%v` %v %v", columnName, querySymbolLeft, columnValueConvert(s.TableColumn, columnName, columnDataLeft, s.LogSeq)))
			}
		}
	}
	return strings.Join(whereLeft, " and ")
}

/*
RightStitching 生成sql 语句where right 条件
*/
func RightStitching(s global.WhereGenerateInput) string {
	var (
		whereRight []string
	)
	for _, v := range getAtomicValue(s.RightDirection) {
		columnName, nRightOk := v["columnName"]
		columnDataRight, cRightOk := v["columnDataRight"]
		querySymbolRight, qRightOk := v["querySymbolRight"]
		if cRightOk && qRightOk && nRightOk {
			if strings.EqualFold(querySymbolRight, "=") && strings.EqualFold(columnDataRight, "NULL") {
				whereRight = append(whereRight, fmt.Sprintf("`%v` %v %v", columnName, " IS ", columnDataRight))
			} else if strings.EqualFold(querySymbolRight, ">") && strings.EqualFold(columnDataRight, "NULL") {
				whereRight = append(whereRight, fmt.Sprintf("`%v` %v %v", columnName, " IS NOT ", columnDataRight))
			} else {
				whereRight = append(whereRight, fmt.Sprintf("`%v` %v %v", columnName, querySymbolRight, columnValueConvert(s.TableColumn, columnName, columnDataRight, s.LogSeq)))
			}
		}
	}
	return strings.Join(whereRight, " and ")
}
func SqlFilterFunc(s global.WhereGenerateInput) (w string) {
	return s.SqlFilter.WhereSql
}
func (my TableSql) OrderBY(s global.WhereGenerateInput) (res string, err error) {
	var orderByColumn []string
	if strings.EqualFold(s.NullConstraint, "null") {
		return
	}
	var uniqOrderByColumn = make(map[string]int)
	for _, v := range s.IndexColumn {
		for _, v1 := range getAtomicValue(s.LeftDirection) {
			if v2, ok := v1["columnName"]; ok {
				if strings.EqualFold(v, v2) {
					if _, ok2 := uniqOrderByColumn[v]; !ok2 {
						uniqOrderByColumn[v]++
						orderByColumn = append(orderByColumn, v)
					}
				}
			}
		}
	}
	if len(orderByColumn) > 0 {
		return fmt.Sprintf("order by `%v`", strings.Join(orderByColumn, "`,`")), nil
	}
	return
}

/*
WhereGenerate  sql 语句where条件生成
*/
func (my TableSql) WhereGenerate(s global.WhereGenerateInput) (ConditionSlice []string, err error) {
	if len(s.SqlFilter.WhereSql) > 0 {
		if v := SqlFilterFunc(s); len(v) > 0 {
			ConditionSlice = append(ConditionSlice, v)
		}
		return
	}
	if v := LeftStitching(s); len(v) > 0 {
		ConditionSlice = append(ConditionSlice, v)
	}
	if v := RightStitching(s); len(v) > 0 {
		ConditionSlice = append(ConditionSlice, v)
	}
	return
}
func columnMetaGroup(s global.SelectColumnInput) (allTableColumn []global.TableMeta) {
	var (
		readObject []string
	)
	switch s.OutputColumnType {
	case "all":
		allTableColumn = s.TableColumn
	case "specify":
		readObject = s.SelectColumn
	default:
		readObject = s.IndexColumn
	}
	for _, v1 := range readObject {
		for _, vv := range s.TableColumn {
			if strings.EqualFold(vv.ColumnName, v1) {
				allTableColumn = append(allTableColumn, vv)
				break
			}
		}
	}
	return
}
func timeTypeToFunc(i global.TableMeta, columnName, asColumn string) (r string) {
	switch i.ConvertColumnType.Format {
	case "year-month-day":
		r = fmt.Sprintf("date_format(%v,'%%Y-%%m-%%d') as `%v`", columnName, asColumn)
	case "hour:minute:second":
		r = fmt.Sprintf("date_format(%v,'%%H:%%i:%%s') as `%v`", columnName, asColumn)
	case "year-month-day hour:minute:second":
		r = fmt.Sprintf("date_format(%v,'%%Y-%%m-%%d %%H:%%i:%%s') as `%v`", columnName, asColumn)
	case "hour:minute:second.milli(1)", "hour:minute:second.milli(2)", "hour:minute:second.milli(3)", "hour:minute:second.micro(1)", "hour:minute:second.micro(2)", "hour:minute:second.micro(3)", "hour:minute:second.nano(1)", "hour:minute:second.nano(2)", "hour:minute:second.nano(3)":
		r = fmt.Sprintf("date_format(%v,'%%H:%%i:%%s.%%f') as `%v`", columnName, asColumn)
	case "year":
		r = fmt.Sprintf("date_format(%v,'%%Y') as `%v`", columnName, asColumn)
	case "year-month-day hour:minute:second.milli(1)", "year-month-day hour:minute:second.milli(2)", "year-month-day hour:minute:second.milli(3)", "year-month-day hour:minute:second.micro(1)", "year-month-day hour:minute:second.micro(2)", "year-month-day hour:minute:second.micro(3)", "year-month-day hour:minute:second.nano(1)", "year-month-day hour:minute:second.nano(2)", "year-month-day hour:minute:second.nano(3)":
		r = fmt.Sprintf("date_format(%v,'%%Y-%%m-%%d %%H:%%i:%%s.%%f') as `%v`", columnName, asColumn)
	default:
		r = fmt.Sprintf("date_format(%v,'%%Y-%%m-%%d %%H:%%i:%%s') as `%v`", columnName, asColumn)
	}
	return
}
func decimalTypeToFunc(i global.TableMeta, columnName, asColumn string) (r string) {
	switch i.ConvertColumnType.Type {
	case "float", "double":
		var decimal []string
		if p, err := strconv.Atoi(i.DataPrecision); err == nil && p > 0 {
			decimal = append(decimal, strconv.Itoa(p))
		}
		if s, err := strconv.Atoi(i.DataScale); err == nil && s > 0 {
			decimal = append(decimal, strconv.Itoa(s))
		}
		if len(decimal) > 0 {
			r = fmt.Sprintf("CAST(%v AS DECIMAL(%v)) as `%v`", columnName, strings.Join(decimal, ","), asColumn)
		} else {
			r = fmt.Sprintf("%v as `%v`", columnName, asColumn)
		}
	default:
		r = fmt.Sprintf("%v as `%v`", columnName, asColumn)
	}
	return
}
func gisTypeToFunc(i global.TableMeta, columnName, asColumn string) (r string) {
	switch strings.ToUpper(i.DataType) {
	case "POINT":
	case "MULTIPOINT":
	case "LINESTRING":
	case "MULTILINESTRING":
	case "POLYGON":
	case "MULTIPOLYGON":
	case "GEOMETRY":
	case "GEOMETRYCOLLECTION":
	}
	return fmt.Sprintf("concat(left(st_asgeojson(%v),length(st_asgeojson(%v)) -1 ),', \"RID\":',st_srid(geom),'}') as `%v`", columnName, columnName, asColumn)
}
func specialTypeToFunc(i global.TableMeta, columnName string) (r string) {
	asColumn := fmt.Sprintf("C%v", i.ColumnSeq)
	switch i.ConvertColumnType.Type {
	case "time":
		return timeTypeToFunc(i, columnName, asColumn)
	case "float", "double", "decimal":
		return decimalTypeToFunc(i, columnName, asColumn)
	case "gis":
		return gisTypeToFunc(i, columnName, asColumn)
	default:
		return fmt.Sprintf("%v as `%v`", columnName, asColumn)
	}
}
func columnNameActive(s global.SelectColumnInput, i global.TableMeta) (r string) {
	r = colNameUnLock(s, i)
	if v1, ok := s.RowsActiveMapList[strings.ToLower("end0")]; ok && strings.EqualFold(v1, "true") {
		r = fmt.Sprintf("0+CAST(%v as char) ", r)
	}
	return
}
func colNameUnLock(s global.SelectColumnInput, i global.TableMeta) (r string) {
	r = fmt.Sprintf("`%v`", i.ColumnName)
	for _, v := range s.Esa.ColumnSlice {
		if strings.EqualFold(v, i.ColumnName) {
			r = fmt.Sprintf(" %v(%v) ", s.Esa.UnLockFuncName, r)
			break
		}
	}
	return
}
func colNameLock(esaCol []string, schema string, lockFunc string, columnName, columnVal string) (r string) {
	r = fmt.Sprintf("%v", columnVal)
	for _, v := range esaCol {
		if strings.EqualFold(v, columnName) && len(lockFunc) > 0 {
			r = fmt.Sprintf("%v.%v(%v)", schema, lockFunc, r)
			break
		}
	}
	return
}
func (my TableSql) SelectColumn(s global.SelectColumnInput) (columnName, asColumnName []string, err error) {
	if strings.EqualFold(strings.Join(s.IndexColumn, ""), "_hidden_pk_") && !strings.EqualFold(s.OutputColumnType, "all") {
		l := strings.Join(s.IndexColumn, "")
		columnName = append(columnName, fmt.Sprintf("%v AS `C%v`", l, l))
		return
	}
	for _, i := range columnMetaGroup(s) {
		asColumnName = append(asColumnName, fmt.Sprintf("C%v", i.ColumnSeq))
		columnName = append(columnName, specialTypeToFunc(i, columnNameActive(s, i)))
	}
	return
}
func (my TableSql) Prefix(s global.PrefixInput) (insertPrefix string, err error) {
	var (
		tableName string
		ignoreStr string
	)
	if s.Insert.Prefix.Ignore {
		//ignoreStr = "IGNORE" //timetimp类型 Incorrect datetime value: '2024-09-26 11:27:28 +0800 08:00' for column
	}
	tableName = fmt.Sprintf(" `%v`.`%v` ", s.Insert.Prefix.TableInfo.Schema, s.Insert.Prefix.TableInfo.Table)
	insertPrefix = fmt.Sprintf("INSERT %v INTO %v (`%v`) VALUES", ignoreStr, tableName, strings.Join(s.Insert.Prefix.InsertColumn, "`,`"))
	if s.Insert.Prefix.LobSwitch {
		insertPrefix = fmt.Sprintf("INSERT %v INTO %v (`%v`) VALUES (%v)", ignoreStr, tableName, strings.Join(s.Insert.Prefix.InsertColumn, "`,`"), strings.Join(s.Insert.Prefix.PlaceholderValue, ","))
	}
	return
}

func colNameMatchRows(p global.PrefixInput, s []*string) (res []*string, err error) {
	var news []*string
	res = s
	if !p.Insert.Value.IgnoreColumnLength {
		if len(p.Insert.Value.ColData) != len(s) {
			err = errors.New(fmt.Sprintf("column length not match"))
			return
		}
	}
	for k := range p.Insert.Value.ColData {
		news = append(news, s[k])
	}
	res = news
	return
}
func specialCharacterEscape(s *string) (r string) {
	r = *s
	if strings.EqualFold(r, "NULL") {
		return
	}
	if strings.Contains(r, "\\") {
		r = strings.ReplaceAll(r, "\\", "\\\\")
	}
	if strings.Contains(r, "'") {
		r = strings.ReplaceAll(r, "'", "\\'")
	}
	if strings.Contains(r, ";") {
		r = strings.ReplaceAll(r, ";", "\\;")
	}
	return
}

/*
Insert 生成insert语句前缀
*/
func (my TableSql) Insert(s global.PrefixInput) (res any, err error) {
	var manyRowsValue []string
	var lobManyRowsValue [][]*string
	var sqlPrefix string
	for _, rows := range s.Insert.Value.RowData {
		if len(rows) == 0 {
			continue
		}
		if rows, err = colNameMatchRows(s, rows); err != nil {
			return
		}
		var singleRowsValue []string
		var lobSingleRowsValue []*string
		for k, v := range rows {
			columnVal := colNameLock(s.Insert.Value.Esa.ColumnSlice, s.Insert.Prefix.TableInfo.Schema, s.Insert.Value.Esa.LockFuncName, s.Insert.Value.ColData[k].ColumnName, specialTypeActive(s.Insert.Prefix.LobSwitch, specialCharacterEscape(v), s.Insert.Value.ColData[k]))
			singleRowsValue = append(singleRowsValue, columnVal)
			lobSingleRowsValue = append(lobSingleRowsValue, v)
		}
		manyRowsValue = append(manyRowsValue, strings.Join(singleRowsValue, ","))
		lobManyRowsValue = append(lobManyRowsValue, lobSingleRowsValue)
	}
	if len(manyRowsValue) == 0 {
		return
	}
	if strings.Contains(s.Insert.Value.Prefix, "(?") {
		return lobManyRowsValue, nil
	}
	if len(manyRowsValue) == 0 {
		return
	}
	sqlPrefix = fmt.Sprintf("%v (%v);", s.Insert.Value.Prefix, strings.Join(manyRowsValue, "),("))
	fmt.Println("------SQL:", sqlPrefix)
	return &sqlPrefix, nil
}
func (my TableSql) Delete(s global.PrefixInput) (any, error) {
	var (
		deleteGather   []*string
		deleteSql      string
		whereColumnMap = make(map[int] /*列序号+ 列名字*/ string)
	)
	for i, v := range s.Delete.Value.ColData {
		switch s.Delete.Value.IndexType {
		case "pri", "uni":
			for m, n := range s.Delete.Value.IndexColumnSeq {
				if strings.EqualFold(fmt.Sprintf("%v", i+1), m) {
					//获取列名和列类型
					whereColumnMap[i] = n
				}
			}
		case "mul":
			//获取列名和列类型
			whereColumnMap[i] = v.ColumnName
		}
	}
	for _, v := range s.Delete.Value.RowData {
		var o []string
		for p, l := range v {
			if c, ok := whereColumnMap[p]; ok {
				o = append(o, fmt.Sprintf(" %v = '%v'", c, specialCharacterEscape(l)))
			}
		}
		if len(o) > 0 {
			deleteSql = fmt.Sprintf("delete from `%v`.`%v` where %v;", s.Delete.Value.TableInfo.Schema, s.Delete.Value.TableInfo.Table, strings.Join(o, " and "))
			deleteGather = append(deleteGather, &deleteSql)
		}
	}
	return deleteGather, nil
}
